﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace outREACH
{
    public class MedResource
    {
        public int MD_ID { get; set; }
        public string userName { get; set; }
        public string medicineType { get; set; }
        public string medicineName { get; set; }
        public int quantity { get; set; }
        public string country { get; set; }
        public string HospitalName { get; set; }
        public DateTime dateTime { get; set; }
        public string category { get; set; }
        public string status { get; set; }
        public int partial { get; set; }
        public string reason { get; set; }

        public static void NewRequest(MedResource mr)
        {
            SqlConnection conn = null;
            int newMedResourcesID = 0;

            try
            {
                conn = new SqlConnection();
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "insert into MedicalResources(userName, medicineType, medicineName, quantity,country, HospitalName, dateTime, category, status, partial, reason) VALUES (@userName, @medicineType, @medicineName, @quantity, @country, @HospitalName, @dateTime, @category, @status, @partial, @reason)";
                comm.Parameters.AddWithValue("@userName", mr.userName);
                comm.Parameters.AddWithValue("@medicineType", mr.medicineType);
                comm.Parameters.AddWithValue("@medicineName", mr.medicineName);
                comm.Parameters.AddWithValue("@dateTime", mr.dateTime);
                comm.Parameters.AddWithValue("@category", mr.category);
                comm.Parameters.AddWithValue("@quantity", mr.quantity);
                comm.Parameters.AddWithValue("@country", mr.country);
                comm.Parameters.AddWithValue("@HospitalName", mr.HospitalName);
                comm.Parameters.AddWithValue("@status", mr.status);
                comm.Parameters.AddWithValue("@partial", mr.partial);
                comm.Parameters.AddWithValue("@reason", mr.reason);
                int rowsAffected = comm.ExecuteNonQuery();
                comm.CommandText = "select @@IDENTITY as newMedResourcesID";
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newMedResourcesID = int.Parse(dr["newMedResourcesID"].ToString());
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static List<MedResource> GetMedResource()
        {
            SqlConnection conn = null;
            List<MedResource> result = new List<MedResource>();

            try
            {
                conn = new SqlConnection();
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "Select * from MedicalResources order by [dateTime] desc";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    MedResource mr = new MedResource();
                    mr.category = (string)dr["category"];
                    mr.dateTime = (DateTime)dr["dateTime"];
                    mr.MD_ID = (int)dr["MD_ID"];
                    mr.quantity = (int)dr["quantity"];
                    mr.medicineName = (string)dr["medicineName"];
                    mr.medicineType = (string)dr["medicineType"];
                    mr.HospitalName = (string)dr["HospitalName"];
                    mr.userName = (string)dr["userName"];
                    mr.status = (string)dr["status"];
                    mr.partial = (int)dr["partial"];
                    mr.reason = (string)dr["reason"];
                    mr.country = (string)dr["country"];
                    result.Add(mr);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static void updateMedRequest(int MD_ID, string status, int partial)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "Update MedicalResources set status = @status, partial = @partial where MD_ID = @MD_ID";
                comm.Parameters.AddWithValue("@MD_ID", MD_ID);
                comm.Parameters.AddWithValue("@status", status);
                comm.Parameters.AddWithValue("@partial", partial);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}